package controleBD;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.LinkedHashSet;
import java.util.Set;

import org.jfree.data.time.Second;
import org.jfree.data.time.TimeSeries;
import org.jfree.data.time.TimeSeriesCollection;
import org.jfree.data.xy.XYDataset;

import modelo.EventosDoUsuario;

public class JoinQueryDAO {

	Connection c = null;
    Statement stmt = null;
    
	
	public ArrayList<EventosDoUsuario> retornaEventos(String usuario, String evento){
	    	
	    	ArrayList<EventosDoUsuario> lista = new ArrayList<EventosDoUsuario>();
	    	
	    	
	    	try {
				Class.forName("org.sqlite.JDBC");
				c = DriverManager.getConnection("jdbc:sqlite:C:\\registro.db");
				c.setAutoCommit(false);
		    
				stmt = c.createStatement();
				
				ResultSet rs;
				
				if (evento==null){
					System.out.println("Evento nulo");
					rs = stmt.executeQuery( 			"SELECT VALOR, HORA, DATA " +
														"FROM EVENTOS, USUARIOEVENTO " +
														"WHERE EVENTOS.VALOR >= USUARIOEVENTO.MIN AND EVENTOS.VALOR <= USUARIOEVENTO.MAX AND USUARIO='"+ usuario +"'");
				}else{
					rs = stmt.executeQuery( 			"SELECT VALOR, HORA, DATA " +
														"FROM EVENTOS, USUARIOEVENTO " +
														"WHERE EVENTOS.VALOR >= USUARIOEVENTO.MIN " +
														"AND EVENTOS.VALOR <= USUARIOEVENTO.MAX " +
														"AND USUARIO='"+ usuario +"' " +
														"AND EVENTOS.TIPO ='"+evento+"' " +
														"AND EVENTOS.TIPO=USUARIOEVENTO.EVENTO " +
														"AND " +
														"((DATA > DATAATUAL) OR " +
														"((DATA = DATAATUAL) AND (HORA >= HORAATUAL)))" +
														"");
				}
				
			    while ( rs.next() ) {
			         
			    	String  valor = rs.getString("VALOR");
			        String  hora =  rs.getString("HORA");
			        String  data =  rs.getString("DATA");
 
			        lista.add(new EventosDoUsuario(valor, hora, data));
			         
			    }
			      
			
	
				c.commit();
				stmt.close();
				c.commit();
				c.close();
				
				
	    	} catch (Exception e) {
				e.printStackTrace();
			}
	    	  
	    	return lista;
	    }
	
	public static XYDataset createDataset(String usuario, String evento) {

		final TimeSeriesCollection dataset = new TimeSeriesCollection();
        
        final TimeSeries s3 = new TimeSeries("Series 3");

        String valor;
        String hora;
        String data;
        
        int sec;
        int min;
        int hour;
        int day;
        int month;
        int year;
		
		try {
		      Class.forName("org.sqlite.JDBC");
		      Connection c = DriverManager.getConnection("jdbc:sqlite:C:\\registro.db");
		      c.setAutoCommit(false);
		      

		      Statement stmt = c.createStatement();
		      //ResultSet rs = stmt.executeQuery( "SELECT * FROM EVENTOS WHERE EVENTOS.TIPO='"+tipo+"';");
		      ResultSet rs;
		      
//		      rs = stmt.executeQuery(	"SELECT AVG(VALOR), HORA, DATA " +
//		      						 	"FROM EVENTOS " +
//		      						 	"WHERE EVENTOS.TIPO='"+evento+"' " +
//		      						 	"GROUP BY DATA;");
		      
		      rs = stmt.executeQuery( 	"SELECT AVG(VALOR), HORA, DATA " +
										"FROM EVENTOS, USUARIOEVENTO " +
										"WHERE EVENTOS.VALOR >= USUARIOEVENTO.MIN " +
										"AND EVENTOS.VALOR <= USUARIOEVENTO.MAX " +
										"AND USUARIO='"+ usuario +"' " +
										"AND EVENTOS.TIPO ='"+evento+"' " +
										"AND EVENTOS.TIPO=USUARIOEVENTO.EVENTO " +
										"GROUP BY DATA;");
		      
		      
		      while ( rs.next() ) {
		    	 
		         valor = rs.getString("AVG(VALOR)");
		         hora =  rs.getString("HORA");
		         data =  rs.getString("DATA");
		         
		         sec = Integer.parseInt(hora.substring(6, 8));
		         min = Integer.parseInt(hora.substring(3, 5));
		         hour = Integer.parseInt(hora.substring(0, 2));
		         
		         
		         day = Integer.parseInt(data.substring(8, 10));
		         month = Integer.parseInt(data.substring(5, 7));
		         year = Integer.parseInt(data.substring(0, 4));
		         

		         s3.add(new Second(sec, min, hour, day, month, year), Double.parseDouble(valor));
		      }
		      rs.close();
		      stmt.close();
		      c.close();
		    } catch ( Exception e ) {
		      System.err.println( e.getClass().getName() + ": " + e.getMessage() );
		      System.exit(0);
		    }
	
		
		
        
        dataset.addSeries(s3);
    
        
        return dataset;
        
	}
	
	public static XYDataset createDatasetDetalhado(String usuario, String evento, String dataAgora) {

		final TimeSeriesCollection dataset = new TimeSeriesCollection();
        
        final TimeSeries s1 = new TimeSeries("Series 1");

        String valor;
        String hora;
        String data;
        
        int sec;
        int min;
        int hour;
        int day;
        int month;
        int year;
		
		try {
		      Class.forName("org.sqlite.JDBC");
		      Connection c = DriverManager.getConnection("jdbc:sqlite:C:\\registro.db");
		      c.setAutoCommit(false);
		      

		      Statement stmt = c.createStatement();
		      ResultSet rs;

		      if (dataAgora == null){
		    	  rs = stmt.executeQuery("SELECT VALOR, HORA, DATA "+
		    			  				"FROM EVENTOS, USUARIOEVENTO "+
		    			  				"WHERE TIPO = '"+evento+"' "+
		    			  				"AND EVENTOS.VALOR >= USUARIOEVENTO.MIN "+
		    			  				"AND EVENTOS.VALOR <= USUARIOEVENTO.MAX "+
		    			  				"AND USUARIO = '"+usuario+"' "+ 
		    			  				"AND EVENTOS.TIPO = USUARIOEVENTO.EVENTO " +
		    			  				"GROUP BY HORA;");
		      }else{
			      dataAgora = dataAgora.substring(6, 10) +"/"+ dataAgora.substring(3, 5)+"/"+dataAgora.substring(0, 2);
		    	  rs = stmt.executeQuery(	"SELECT VALOR, HORA, DATA "+
		    			  					"FROM EVENTOS, USUARIOEVENTO "+
		    			  					"WHERE TIPO = '"+evento+"' "+
		    			  					"AND DATA = '"+dataAgora+"' "+
		    			  					"AND EVENTOS.VALOR >= USUARIOEVENTO.MIN "+
		    			  					"AND EVENTOS.VALOR <= USUARIOEVENTO.MAX "+
		    			  					"AND USUARIO = '"+usuario+"' "+ 
		    			  					"AND EVENTOS.TIPO = USUARIOEVENTO.EVENTO " +
		    			  					"GROUP BY HORA;");
		      }
		     
		      int i = 0;
		      
      
		      while ( rs.next() ) {
		    	 
		         valor = rs.getString("VALOR");
		         hora =  rs.getString("HORA");
		         data =  rs.getString("DATA");
		         
		         sec = Integer.parseInt(hora.substring(6, 8));
		         min = Integer.parseInt(hora.substring(3, 5));
		         hour = Integer.parseInt(hora.substring(0, 2));
		         
		         
		         day = Integer.parseInt(data.substring(8, 10));
		         month = Integer.parseInt(data.substring(5, 7));
		         year = Integer.parseInt(data.substring(0, 4));
		         
		         boolean completo = false;
	        	 if ((i<20)&&(completo == false)){
	        		 s1.add(new Second(sec, min, hour, day, month, year), Double.parseDouble(valor));
		         }else if(i >= 20){
		        	 s1.add(new Second(sec, min, hour, day, month, year), Double.parseDouble(valor));
		        	 if(completo==false)completo = true;
		        	 i=0;
 		         }
		         i++;
		           
		      }
		      rs.close();
		      stmt.close();
		      c.close();
		    } catch ( Exception e ) {
		      System.err.println( e.getClass().getName() + ": " + e.getMessage() );
		      System.exit(0);
		    }
	
		
		
        
        dataset.addSeries(s1);
    
        
        return dataset;
        
	}
	
	public Set<String> retornaDatas(String evento){
		
		Set<String> set = new LinkedHashSet<String>();
		
		try {
		      Class.forName("org.sqlite.JDBC");
		      Connection c = DriverManager.getConnection("jdbc:sqlite:C:\\registro.db");
		      c.setAutoCommit(false);
		      

		      Statement stmt = c.createStatement();
		      ResultSet rs;
		      
		      rs = stmt.executeQuery(	"SELECT DATA " +
										"FROM EVENTOS " +
										"WHERE TIPO='"+evento+"';");
		      
		      String data; 
		      while ( rs.next() ) {
		    	  data = rs.getString("DATA");
		    	  set.add(data.substring(8,10)+"/"+data.substring(5,7)+"/"+data.substring(0,4));
		      }
		      rs.close();
		      stmt.close();
		      c.close();
		    } catch ( Exception e ) {
		      System.err.println( e.getClass().getName() + ": " + e.getMessage() );
		      System.exit(0);
		    }
		
		return set;
	}
}
